<?php
	class vanbandendb
	{
		public function Add($SovaKyHieu, $TrichYeu, $MaLoaiVanBan,$NgayBanHanh,$NgayHetHan, $MaTrangThai, $NoiBanHanh)
		{
			$sql = " INSERT INTO `tbl_vanbanden`(`SovaKyHieu`, `TrichYeu`, `MaLoaiVanBan`, `MaTrangThai`, `NgayBanHanh`, `NgayHetHan`, `NgayDen`, `NoiBanHanh`) VALUES ('$SovaKyHieu', '$TrichYeu', $MaLoaiVanBan, $MaTrangThai, STR_TO_DATE('$NgayBanHanh','%d,%m,%Y') , STR_TO_DATE('$NgayHetHan','%d,%m,%Y'),CURDATE(), '$NoiBanHanh')";
			$data = new dataservice();
			return $data->ExecuteNonQuery($sql);
		}
		
		public function Delete($MaVanBanDen)
		{
			$sql = " DELETE FROM `tbl_vanbanden` WHERE MaVanBanDen = '$MaVanBanDen' ";
			$data = new dataservice();
			return $data->ExecuteNonQuery($sql);
		}
		
		public function Update($MaVanBanDen, $SovaKyHieu, $TrichYeu, $MaLoaiVanBan,$NgayBanHanh,$NgayHetHan, $NoiBanHanh)
		{
			$sql = " UPDATE `tbl_vanbanden` SET 
			`SovaKyHieu` = '$SovaKyHieu',
			`TrichYeu` = '$TrichYeu' ,
			`MaLoaiVanBan`= $MaLoaiVanBan,
			`NgayBanHanh`= STR_TO_DATE('$NgayBanHanh','%d,%m,%Y') ,
			`NgayHetHan`= STR_TO_DATE('$NgayHetHan','%d,%m,%Y'),
			`NoiBanHanh`= '$NoiBanHanh'
			WHERE `MaVanBanDen`= '$MaVanBanDen' ";
			
			$data = new dataservice();
			return $data->ExecuteNonQuery($sql);
		}
		
		public function GetList($MaVanBanDen, $SovaKyHieu, $NgayBanHanh, $NgayHetHan, $TrichYeu, $MaLoaiVanBan, $MaTrangThai, $NgayDen, $ThoiGianDuyet, $NoiBanHanh)
		{
			$sql = " SELECT * FROM `tbl_vanbanden` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai ";
			if($MaVanBanDen != "")
				$sql .= " AND vb.MaVanBanDen = '$MaVanBanDen' ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($TrichYeu != "")
				$sql .= " AND vb.TrichYeu LIKE '%$TrichYeu%'";
			if($MaLoaiVanBan != "")
				$sql .= " AND vb.MaLoaiVanBan = $MaLoaiVanBan";
			if($MaTrangThai != "")
				$sql .= " AND vb.MaTrangThai = '$MaTrangThai' ";
			if($NgayDen != "")
				$sql .= " AND vb.NgayDen = STR_TO_DATE('$NgayDen','%d,%m,%Y')";
			if($ThoiGianDuyet != "")
				$sql .= " AND vb.ThoiGianDuyet = STR_TO_DATE('$ThoiGianDuyet','%d,%m,%Y')";
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%'";	
			
			$sql .= "ORDER BY vb.MaVanBanDen DESC" ;
			
			$data = new dataservice();
			return $data->ExecuteQuery($sql);
		}
		
		public function PageList($MaVanBanDen, $SovaKyHieu, $NgayBanHanh, $NgayHetHan, $TrichYeu, $MaLoaiVanBan, $MaTrangThai, $NgayDen, $ThoiGianDuyet, $NoiBanHanh, $start, $limit)
		{
			$sql = " SELECT * FROM `tbl_vanbanden` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai ";
			if($MaVanBanDen != "")
				$sql .= " AND vb.MaVanBanDen = '$MaVanBanDen' ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($TrichYeu != "")
				$sql .= " AND vb.TrichYeu LIKE '%$TrichYeu%'";
			if($MaLoaiVanBan != "")
				$sql .= " AND vb.MaLoaiVanBan = $MaLoaiVanBan";
			if($MaTrangThai != "")
				$sql .= " AND vb.MaTrangThai = '$MaTrangThai' ";
			if($NgayDen != "")
				$sql .= " AND vb.NgayDen = STR_TO_DATE('$NgayDen','%d,%m,%Y')";
			if($ThoiGianDuyet != "")
				$sql .= " AND vb.ThoiGianDuyet = STR_TO_DATE('$ThoiGianDuyet','%d,%m,%Y')";
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%'";	
				
			$sql .= "ORDER BY vb.MaVanBanDen DESC" ;
			
			$sql .= " LIMIT $start, $limit ";
			
			$data = new dataservice();
			return $data->ExecuteQuery($sql);
		}
		
		
		public function GetTrangThaiVanBan($MaVanBanDen, $MaPhongXuLy, $MaNguoiXuLy, $MaTrangThai, $MaNguoiDuyet, $SovaKyHieu, $NgayBanHanh, $NgayHetHan,  $NoiBanHanh)
		{
			if($MaNguoiDuyet != "")
				$sql = " SELECT * FROM `tbl_vanbanden` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt, `tbl_nguoidung` ng WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai  AND ng.MaNguoiDung = vb.MaNguoiDuyet AND vb.MaNguoiDuyet = '$MaNguoiDuyet' ";	
			else
			$sql = " SELECT * FROM `tbl_vanbanden` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai ";
			
			if($MaVanBanDen != "")
				$sql .= " AND vb.MaVanBanDen = '$MaVanBanDen' ";
			
			if($MaPhongXuLy != "")
				$sql .= " AND vb.MaPhongXuLy = '$MaPhongXuLy' ";	
			if($MaNguoiXuLy != "")
				$sql .= " AND vb.MaNguoiXuLy = '$MaNguoiXuLy' ";	
			if($MaTrangThai != "")
				$sql .= " AND vb.MaTrangThai = '$MaTrangThai' ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%'";	
					
			$data = new dataservice();
			return $data->ExecuteQuery($sql);
		}
		
		public function pageTrangThaiVanBan($MaVanBanDen, $MaPhongXuLy, $MaNguoiXuLy, $MaTrangThai, $MaNguoiDuyet, $SovaKyHieu, $NgayBanHanh, $NgayHetHan,  $NoiBanHanh, $start, $limit)
		{
			if($MaNguoiDuyet != "")
				$sql = " SELECT * FROM `tbl_vanbanden` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt, `tbl_nguoidung` ng WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai  AND ng.MaNguoiDung = vb.MaNguoiDuyet AND vb.MaNguoiDuyet = '$MaNguoiDuyet' ";	
			else
			$sql = " SELECT * FROM `tbl_vanbanden` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai ";
			
			if($MaVanBanDen != "")
				$sql .= " AND vb.MaVanBanDen = '$MaVanBanDen' ";
			
			if($MaPhongXuLy != "")
				$sql .= " AND vb.MaPhongXuLy = '$MaPhongXuLy' ";	
			if($MaNguoiXuLy != "")
				$sql .= " AND vb.MaNguoiXuLy = '$MaNguoiXuLy' ";	
			if($MaTrangThai != "")
				$sql .= " AND vb.MaTrangThai = '$MaTrangThai' ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%'";	
			$sql .= "  ORDER BY vb.MaVanBanDen DESC LIMIT $start, $limit ";
			$data = new dataservice();
			return $data->ExecuteQuery($sql);
		}
		
		
		public function ChangeTrangThai($MaVanBanDen, $MaNguoiDuyet, $MaTrangThai, $MaPhongXuLy, $MaNguoiXuLy )
		{
			$sql = " UPDATE `tbl_vanbanden` SET 
			`MaTrangThai` = $MaTrangThai ";
			if($MaNguoiDuyet != "")
			$sql .= " ,`MaNguoiDuyet` = $MaNguoiDuyet,  ThoiGianDuyet = CURDATE() ";
			if($MaPhongXuLy != "")
			$sql .= " ,`MaPhongXuLy` = $MaPhongXuLy ";
			if($MaNguoiXuLy != "")
			$sql .= " ,`MaNguoiXuLy` = $MaNguoiXuLy ";
			$sql .= " WHERE `MaVanBanDen`= '$MaVanBanDen' ";
			$data = new dataservice();
			return $data->ExecuteNonQuery($sql);
		}
		
		
	}
?>